Imports System.Data.SqlClient

Public Class <OwnerU>_<TableU>DataClass

    Public Function SelectAll() As DataTable
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim selectProcedure As String = "[<Owner>].[<TableU>SelectAll]"
        Dim selectCommand As New SqlCommand(selectProcedure, connection)
        selectCommand.CommandType = CommandType.StoredProcedure
        Dim dt As New DataTable
        Try
            connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader()
            If reader.HasRows Then
                dt.Load(reader)
            End If
            reader.Close()
        Catch ex As SqlException
            Return dt
        Finally
            connection.Close()
        End Try
        Return dt
    End Function

    Public Function Search(ByVal sField As String, ByVal sCondition As String, ByVal sValue As String) As DataTable
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim selectProcedure As String = "[<Owner>].[<TableU>Search]"
        Dim selectCommand As New SqlCommand(selectProcedure, connection)
        selectCommand.CommandType = CommandType.StoredProcedure
    <If Loop>
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsRelated>
	       <And>
	       <IsMiscTag>
        selectCommand.Parameters.AddWithValue("@<RDisplayField><MiscTag>", IIf(sField = "<FieldC>", sValue, DBNull.Value))
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsRelated>
	       <And>
	       <IsNotMiscTag>
        selectCommand.Parameters.AddWithValue("@<RDisplayField>", IIf(sField = "<FieldC>", sValue, DBNull.Value))
   	<IsNotText>
   	<And>
   	<IsNotNText>
        <And>
        <IsNotRelated>
        selectCommand.Parameters.AddWithValue("@<FieldU>", IIf(sField = "<FieldC>", sValue, DBNull.Value))
    </If Loop>
        selectCommand.Parameters.AddWithValue("@SearchCondition", sCondition)
        Dim dt As New DataTable
        Try
            connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader()
            If reader.HasRows Then
                dt.Load(reader)
            End If
            reader.Close()
        Catch ex As SqlException
            Return dt
        Finally
            connection.Close()
        End Try
        Return dt
    End Function

    Public Function Select_Record(ByVal cls<OwnerU>_<TableU>Para As <OwnerU>_<TableU>Class) As <OwnerU>_<TableU>Class
        Dim cls<OwnerU>_<TableU> As New <OwnerU>_<TableU>Class
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim selectProcedure As String = "[<Owner>].[<TableU>Select]"
        Dim selectCommand As New SqlCommand(selectProcedure, connection)
        selectCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsPKey>
        selectCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>Para.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotPKey>
           <And>
           <IsIdentity>
        selectCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>Para.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotIdentityCount>
           <And>
           <IsNotPKeyCount>
   	   <And>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
        selectCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>Para.<FieldU>)
           <ExitLoop>
        </If Loop>
        Try
            connection.Open()
            Dim reader As SqlDataReader = selectCommand.ExecuteReader(CommandBehavior.SingleRow)
            If reader.Read Then
                With cls<OwnerU>_<TableU>
                <If Loop>
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsFloat>
                    .<FieldU> = If(IsDBNull(reader("<FieldU>")), Nothing, CType(CType(reader("<FieldU>"), Double?), Decimal?))
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsReal>
                    .<FieldU> = If(IsDBNull(reader("<FieldU>")), Nothing, CType(CType(reader("<FieldU>"), Single?), Decimal?))
                    <IsNull>
                    <And>
                    <IsNullable>
                    <And>
                    <IsNotFloat>
                    <And>
                    <IsNotReal>
                    .<FieldU> = If(IsDBNull(reader("<Field>")), Nothing, CType(reader("<Field>"), <VBType>?))
                    <IsNull>
                    <And>
                    <IsNotNullable>
                    .<FieldU> = If(IsDBNull(reader("<Field>")), Nothing, reader("<Field>").ToString)
                    <IsNotNull>
                    .<FieldU> = <Convert>(reader("<Field>"))
                </If Loop>
                End With
            Else
                cls<OwnerU>_<TableU> = Nothing
            End If
            reader.Close()
        Catch ex As SqlException
            Return cls<OwnerU>_<TableU>
        Finally
            connection.Close()
        End Try
        Return cls<OwnerU>_<TableU>
    End Function

    Public Function Add(ByVal cls<OwnerU>_<TableU> As <OwnerU>_<TableU>Class) As Boolean
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim insertProcedure As String = "[<Owner>].[<TableU>Insert]"
        Dim insertCommand As New SqlCommand(insertProcedure, connection)
        insertCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        insertCommand.Parameters.AddWithValue("@<FieldU>", IIf(cls<OwnerU>_<TableU>.<FieldU>.HasValue, cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        insertCommand.Parameters.AddWithValue("@<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>.<FieldU>), cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNotNull>
        insertCommand.Parameters.AddWithValue("@<FieldU>", cls<OwnerU>_<TableU>.<FieldU>)
        </If Loop>
        insertCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int)
        insertCommand.Parameters("@ReturnValue").Direction = ParameterDirection.Output
        Try
            connection.Open()
            insertCommand.ExecuteNonQuery()
            Dim count As Integer = System.Convert.ToInt32(insertCommand.Parameters("@ReturnValue").Value)
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            Return False
        Finally
            connection.Close()
        End Try
        Return True
    End Function

    Public Function Update(ByVal cls<OwnerU>_<TableU>Old As <OwnerU>_<TableU>Class, _
           ByVal cls<OwnerU>_<TableU>New As <OwnerU>_<TableU>Class) As Boolean
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim updateProcedure As String = "[<Owner>].[<TableU>Update]"
        Dim updateCommand As New SqlCommand(updateProcedure, connection)
        updateCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", IIf(cls<OwnerU>_<TableU>New.<FieldU>.HasValue, cls<OwnerU>_<TableU>New.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>New.<FieldU>), cls<OwnerU>_<TableU>New.<FieldU>, DBNull.Value))
           <IsNotIdentity>
           <And>
           <IsNotNull>
        updateCommand.Parameters.AddWithValue("@New<FieldU>", cls<OwnerU>_<TableU>New.<FieldU>)
        </If Loop>
        <If Loop>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNotNull>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", cls<OwnerU>_<TableU>Old.<FieldU>)
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(cls<OwnerU>_<TableU>Old.<FieldU>.HasValue, cls<OwnerU>_<TableU>Old.<FieldU>, DBNull.Value))
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        updateCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>Old.<FieldU>), cls<OwnerU>_<TableU>Old.<FieldU>, DBNull.Value))
        </If Loop>
        updateCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int)
        updateCommand.Parameters("@ReturnValue").Direction = ParameterDirection.Output
        Try
            connection.Open()
            updateCommand.ExecuteNonQuery()
            Dim count As Integer = System.Convert.ToInt32(updateCommand.Parameters("@ReturnValue").Value)
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            Return False
        Finally
            connection.Close()
        End Try
        Return True
    End Function

    Public Function Delete(ByVal cls<OwnerU>_<TableU> As <OwnerU>_<TableU>Class) As Boolean
        Dim connection As SqlConnection = <Database>DataClass.GetConnection
        Dim deleteProcedure As String = "[<Owner>].[<TableU>Delete]"
        Dim deleteCommand As New SqlCommand(deleteProcedure, connection)
        deleteCommand.CommandType = CommandType.StoredProcedure
        <If Loop>
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNotNull>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", cls<OwnerU>_<TableU>.<FieldU>)
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNullable>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(cls<OwnerU>_<TableU>.<FieldU>.HasValue, cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
           <IsNotText>
           <And>
           <IsNotNText>
           <And>
           <IsNotXML>
           <And>
           <IsNull>
           <And>
           <IsNotNullable>
        deleteCommand.Parameters.AddWithValue("@Old<FieldU>", IIf(Not IsNothing(cls<OwnerU>_<TableU>.<FieldU>), cls<OwnerU>_<TableU>.<FieldU>, DBNull.Value))
        </If Loop>
        deleteCommand.Parameters.Add("@ReturnValue", System.Data.SqlDbType.Int)
        deleteCommand.Parameters("@ReturnValue").Direction = ParameterDirection.Output
        Try
            connection.Open()
            deleteCommand.ExecuteNonQuery()
            Dim count As Integer = System.Convert.ToInt32(deleteCommand.Parameters("@ReturnValue").Value)
            If count > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As SqlException
            Return False
        Finally
            connection.Close()
        End Try
        Return True
    End Function

End Class
